DROP TABLE INTEX.QVW_VKPOS
GO
CREATE TABLE QVW_VKPOS
AS
SELECT DISTINCT
VKPMANDANT AS MANDANT,
VKKVB,VKKVG,
SUBSTR(VKPNEUDAT,1,4) AS NYEAR,
SUBSTR(VKPNEUDAT,5,2) AS NMONTH,
SUBSTR(VKPNEUDAT,7,2) AS NDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPNEUDAT,5,2))/3) AS NQUARTER,
 'K'||VKPAUFKRS||VKPAUFLFD AS VKKAUF,'K' AS AUFTYP,
 'K'||VKPAUFKRS||VKPAUFLFD||VKPAUFPOS AS VKPAUF
 ,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPNEUDAT,VKPNEUBEN,VKPKUNDNR AS KDNR,VKPSACHBE, VKPPOSTYP,
VKPARTST AS ARTST,VKPKOLL,VKPART,VKPFARBE,VKPGROE,VKPKOLL||VKPART AS VART,
VKPKOLL||VKPART||VKPFARBE AS VFARBE,
VKPKOLL||VKPART||VKPFARBE||VKPGROE AS VASKU,
VKPPRGR AS PRGR,VKPARTGRP AS ARTGRP,
PART,PVAR,PCOLOR,PDESS,PDCWY,PFINISH,
VKPMEEH,VKPPREH,
ROUND( VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001),2)  AS VKPPREIS,
 PFCOST,
ROUND(VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001)-PFCOST,2) AS GPROFIT,
VKKKURS*0.0001 AS EXCHANGERATE,
ROUND(VKPMENGE1*0.01*VKPMEEHFKT*0.000001*VKPPREIS*0.001*VKKKURS*0.0001,2)  AS VKPVALUE,
ROUND(VKPMENGE1*0.01*VKPMEEHFKT*0.000001,2) AS VKPQTY,
ROUND(VKPRESERV*0.01*VKPMEEHFKT*0.000001,2) AS VKPRESERV,
ROUND(VKPZUGET*0.01*VKPMEEHFKT*0.000001,2) AS VKPZUGET,
ROUND(VKPFREIGE*0.01*VKPMEEHFKT*0.000001,2) AS VKPFREIGE,
ROUND(VKPKOMMISS*0.01*VKPMEEHFKT*0.000001,2) AS VKPKOMMISS,
ROUND(VKPVERS*0.01*VKPMEEHFKT*0.000001,2) AS VKPVERS,
ROUND(VKPFAKT*0.01*VKPMEEHFKT*0.000001,2) AS VKPFAKT,
SUBSTR(VKPWUTERM,1,4) AS SYEAR,
SUBSTR(VKPWUTERM,5,2) AS SMONTH,
SUBSTR(VKPWUTERM,7,2) AS SDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPWUTERM,5,2))/3) AS SQUARTER,
SUBSTR(VKPLIEFTERM,1,4) AS DYEAR,
SUBSTR(VKPLIEFTERM,5,2) AS DMONTH,
SUBSTR(VKPLIEFTERM,7,2) AS DDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPLIEFTERM,5,2))/3) AS DQUARTER,
SUBSTR(VKPFREITERM,1,4) AS FYEAR,
SUBSTR(VKPFREITERM,5,2) AS FMONTH,
SUBSTR(VKPFREITERM,7,2) AS FDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPFREITERM,5,2))/3) AS FQUARTER,
SUBSTR(VKPLVDAT,1,4) AS VYEAR,
SUBSTR(VKPLVDAT,5,2) AS VMONTH,
SUBSTR(VKPLVDAT,7,2) AS VDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPLVDAT,5,2))/3) AS VQUARTER,
VKPWVTERM,VKPWUTERM,VKPFREITERM,VKPVERSTERM,VKPLIEFTERM,VKPFRDAT,VKPZKDAT,VKPLVDAT,VKPERL
FROM VKPOS JOIN VKKOPF ON VKPMANDANT=VKKMANDANT AND VKPAUFKRS=VKKAUFKRS AND VKPAUFLFD=VKKAUFLFD
JOIN QVW_VAFARBEN F ON  VKPKOLL=VAFKOLL AND VKPART=F.VAFART AND VKPFARBE=VAFFARBE AND VKPGROE=VAFPGROE
 WHERE VKPMANDANT='00' AND VKKSA='  'AND  VKPSA='  '   AND VKPMEEHFKT<>'000000000000'
--  AND VKPNEUDAT>'20130101'
GO

